package DAO;

import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.List;
import model.Course;
import util.DataUtil;

public class CourseDAO {

    public static List<Course> getAll() {
        List<Course> list = new ArrayList<Course>();
        String call = "{Call GET_ALL_COURSE}";
        try {
            CallableStatement cs = DataUtil.getConnection().prepareCall(call);
            ResultSet rs = cs.executeQuery();
            while (rs.next()) {
                Course crs = new Course();
                crs.setCourseID(rs.getInt("CourseID"));
                crs.setCourseName(rs.getString("CourseName"));
                crs.setFees(rs.getInt("Fees"));
                crs.setDuration(rs.getInt("Duration"));
                crs.setDescription(rs.getString("Description"));
                list.add(crs);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return list;
    }

    public static boolean AddCourse(Course crs) {
        int rs = 0;
        String call = "{Call ADD_COURSE(?,?,?,?)}";
        try {
            CallableStatement cs = DataUtil.getConnection().prepareCall(call);
            cs.setString(1, crs.getCourseName());
            cs.setInt(2, crs.getFees());
            cs.setInt(3, crs.getDuration());
            cs.setString(4, crs.getDescription());
            rs = cs.executeUpdate();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return (rs > 0) ? true : false;
    }

    public static boolean checkNameCourse(String name) {
        boolean fl = false;
        String call = "{Call CHECK_NAME_COURSE(?)}";
        try {
            CallableStatement cs = DataUtil.getConnection().prepareCall(call);
            cs.setString(1, name);
            ResultSet rs = cs.executeQuery();
            if (rs.next()) {
                fl = true;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return fl;
    }

    public static Course getCouseByName(String name) {
        String call = "{Call GET_COURSE_BY_NAME(?)}";
        Course crs = new Course();
        try {
            CallableStatement cs = DataUtil.getConnection().prepareCall(call);
            cs.setString(1, name);
            ResultSet rs = cs.executeQuery();
            while (rs.next()) {
                crs.setCourseID(rs.getInt("CourseID"));
                crs.setCourseName(rs.getString("CourseName"));
                crs.setFees(rs.getInt("Fees"));
                crs.setDuration(rs.getInt("Duration"));
                crs.setDescription(rs.getString("Description"));
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return crs;
    }

    public static boolean removeCourse(String name) {
        boolean fl = false;
        String call = "{Call REMOVE_COURSE(?)}";
        try {
            CallableStatement cs = DataUtil.getConnection().prepareCall(call);
            cs.setString(1, name);
            if (cs.executeUpdate() > 0) {
                fl = true;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return fl;
    }

    public static boolean editCourse(Course crs) {
        int rs = 0;
        String call = "{Call EDIT_COURSE(?,?,?,?,?)}";
        try {
            CallableStatement cs = DataUtil.getConnection().prepareCall(call);
            cs.setInt(1, crs.getCourseID());
            cs.setString(2, crs.getCourseName());
            cs.setInt(3, crs.getFees());
            cs.setInt(4, crs.getDuration());
            cs.setString(5, crs.getDescription());
            rs = cs.executeUpdate();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return (rs > 0) ? true : false;
    }
    
    public static void sort(int type, List<Course> list) {
        Collections.sort(list, new Comparator<Course>() {
            @Override
            public int compare(Course sv1, Course sv2) {
                int kq = 0;
                
                if (type == 2) {
                    kq = (sv1.getCourseName().compareTo(sv2.getCourseName()));
                } else if (type == 1) {
                    kq = (sv2.getCourseName().compareTo(sv1.getCourseName()));
                } else if (type == 3) {
                    if (sv1.getCourseID() > sv2.getCourseID()) {
                        kq = 1;
                    } else {
                        if (sv1.getCourseID() == sv2.getCourseID()) {
                            kq = 0;
                        } else {
                            kq = -1;
                        }
                    }
                }else if (type == 4) {
                    if (sv1.getCourseID()< sv2.getCourseID()) {
                        kq = 1;
                    } else {
                        if (sv1.getCourseID() == sv2.getCourseID()) {
                            kq = 0;
                        } else {
                            kq = -1;
                        }
                    }
                }
                return kq;
            }
        });
    }
    
    
    
    public static List<Course> search(String filter,String key,List<Course> listCourse){
        List<Course> list = new ArrayList<Course>();
        for (Course c : listCourse) {
            if (filter.equals("Fees")) {
                if (((c.getFees()+"").toLowerCase()).contains(key.toLowerCase())) {
                    list.add(c);
                }
            }
            if(filter.equals("Course ID")){
                if (((c.getCourseID() + "").toLowerCase()).contains(key.toLowerCase())) {
                    list.add(c);
                }
            }
            if(filter.equals("Course Name")){
                if ((c.getCourseName().toLowerCase()).contains(key.toLowerCase())) {
                    list.add(c);
                }
            }
        }
        return list;
    }
}
